package com.oocl.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.swing.text.StyledEditorKit.ForegroundAction;

import org.junit.experimental.theories.FromDataPoints;

import com.oocl.dao.BookDao;
import com.oocl.dao.pojo.Book;

import util.DBUtil;

public class BookDaoImpl implements BookDao{

	public PreparedStatement pst;
	
	public int addBook(Book b) {
		String sql="insert into book(id,bname,pub_date,author,price,is_new,publisher) values (?,?,?,?,?,?,?)";			
			
		Connection con=null;
		int m=0;
		try {
			con=DBUtil.createConnectionWitDatasource();
			pst=con.prepareStatement(sql);
			pst.setInt(1, b.getId());
			pst.setString(2, b.getName());
			pst.setDate(3, new java.sql.Date(b.getPubDate().getTime()));
			pst.setString(4, b.getAuthor());
			pst.setDouble(5, b.getPrice());
			pst.setBoolean(6, b.isNew());
			pst.setString(7, b.getPublisher());
			m = pst.executeUpdate();          //返回值为语句执行后影响的数据库中数据条数
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBUtil.close(con, pst, null);
		}	
		return m;
	
		
	}

	public int deleteBook(Integer id) {

		String sql="delete from book where id=?";			
		PreparedStatement pst=null;	
		Connection con=null;
		int m=0;
		try {
			con=DBUtil.creatConnection();
			pst=con.prepareStatement(sql);
			pst.setInt(1, id);
			m = pst.executeUpdate();          //返回值为语句执行后影响的数据库中数据条数
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBUtil.close(con, pst, null);
		}	
		return m;
	
		
	
	}

	public int updateBook(Book b) {
		String sql = "update book set id=?,bname=?,pub_date=?,author=?,price=?,is_new=?,publisher=?";
		PreparedStatement pst=null;	
		Connection con=null;
		int m=0;
		try {
			con=DBUtil.creatConnection();
			pst=con.prepareStatement(sql);
			pst.setInt(1, b.getId());
			pst.setString(2, b.getName());
			pst.setDate(3, new java.sql.Date(b.getPubDate().getTime()));
			pst.setString(4, b.getAuthor());
			pst.setDouble(5, b.getPrice());
			pst.setBoolean(6, b.isNew());
			pst.setString(7, b.getPublisher());
			m = pst.executeUpdate();          //返回值为语句执行后影响的数据库中数据条数
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBUtil.close(con, pst, null);
		}	
		return m;		
	}

	public Book loadBook(Integer id) {
		String sql="select * from book where id =?";
		PreparedStatement pst = null;
		Connection con=null;
		ResultSet rs=null;
		Book b=null;
		int m=0;
		try {
			con=DBUtil.creatConnection();
			pst=con.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			rs.next();
			b=new Book(rs.getInt("id"), rs.getString("bname"), rs.getDate("pub_date"), rs.getString("author"), rs.getDouble("price"), rs.getBoolean("is_new"), rs.getString("publisher"));	
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBUtil.close(con, pst, null);
		}
		return b;
		
	}

	public List<Book> findAll() {
		// TODO Auto-generated method stub
		List<Book> bList=new ArrayList<Book>();
		String sql="select * from book";
		PreparedStatement pst = null;
		Connection con=null;
		ResultSet rs=null;
		Book b=null;
		int m=0;
		try {
			con=DBUtil.creatConnection();
			pst=con.prepareStatement(sql);
			rs = pst.executeQuery();
			while(rs.next()) {
			b=new Book(rs.getInt("id"), rs.getString("bname"), rs.getDate("pub_date"), rs.getString("author"), rs.getDouble("price"), rs.getBoolean("is_new"), rs.getString("publisher"));	
			bList.add(b);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBUtil.close(con, pst, null);
		}
		return bList;
	}

	public void batchAddBook(List<Book> bList) {                     //使用批处理和事务管理
		String sql = "insert into book(id,bname,pub_date,author,price,is_new,publisher) values (?,?,?,?,?,?,?)";
		PreparedStatement pst=null;	
		Connection con=DBUtil.creatConnection();
		try {
			pst=con.prepareStatement(sql);
			for (int i = 0; i < bList.size(); i++) {
				pst.setInt(1, bList.get(i).getId());
				pst.setString(2,bList.get(i).getName());
				pst.setDate(3, new java.sql.Date( bList.get(i).getPubDate().getTime()));
				pst.setString(4,bList.get(i).getAuthor());
				pst.setDouble(5,bList.get(i).getPrice());
				pst.setBoolean(6,bList.get(i).isNew());
				pst.setString(7, bList.get(i).getPublisher());
				pst.addBatch();
				if (i%2000==0) {
					pst.executeBatch();
					pst.clearBatch();
				}
				
			}
			pst.executeBatch();
			pst.clearBatch();
			con.commit();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.close(con, pst, null);
		}

		
		
		
	}

}
